package com.zrgj.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {

    /*
     * 需要成员变量
     */
    private Connection conn=null;

    private String driver,
            url,
            username,
            password;
    private boolean autoCommit;


    public DBUtil(String driver,String url,String username,String password) throws ClassNotFoundException, SQLException{
        this.driver=driver;
        this.url=url;
        this.username=username;
        this.password=password;
    }



    /**
     * 该方法创建的连接是自动提交的
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void connect() throws ClassNotFoundException, SQLException{
        connect(driver, url, username, password,true);
    }
    /**
     * 创建连接，可设置是否自动提交
     * @param driver
     * @param url
     * @param username
     * @param password
     * @param autoCommit
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void connect(String driver,String url,String username,String password,boolean autoCommit) throws ClassNotFoundException, SQLException{
        Class.forName(driver);
        conn=DriverManager.getConnection(url, username, password);
        conn.setAutoCommit(autoCommit);
    }

//	public void connect(String driver,String url,String username,String password) {
//		try{
//			Class.forName(driver);
//			conn=DriverManager.getConnection(url, username, password);
//		}catch(Exception e){
//			e.printStackTrace();
//		}
//	}

    /**
     * 可以执行增删改3种sql语句
     * @param sql
     * @param objects
     * @return
     * @throws SQLException
     */
    public int update(String sql,Object ...objects ) throws SQLException{
        int res=0;
        PreparedStatement ps=null;
        try{
            ps=conn.prepareStatement(sql);
            if(objects!=null && objects.length>0){
                for (int i=0;i<objects.length;i++){
                    /*
                     * 数组从0开始，objects[i]
                     * setxxx()从1开始，所以应该是i+1
                     */
                    ps.setObject(i+1, objects[i]);
                }
            }
            res=ps.executeUpdate();
        }finally{
            if(ps!=null){
                ps.close();
            }
        }

        return res;

    }

    public List query(String sql,Object ...objects ) throws SQLException{

        PreparedStatement ps=null;
        ResultSet rs=null;
        List resList=new ArrayList();
        try{
            ps=conn.prepareStatement(sql);
            //循环绑定参数
            if(objects!=null && objects.length>0){
                for (int i=0;i<objects.length;i++){
                    /*
                     * 数组从0开始，objects[i]
                     * setxxx()从1开始，所以应该是i+1
                     */
                    ps.setObject(i+1, objects[i]);
                }
            }
            rs=ps.executeQuery();
            ResultSetMetaData rsmd=rs.getMetaData();
            int columnNum=rsmd.getColumnCount();
            //循环取结果集
            while(rs.next()) {
                Object[] array=new Object[columnNum];

                //getxxx()从1开始
                for(int i=1;i<=columnNum;i++){

                    Object res=rs.getObject(i);
                    //数组从0开始
                    array[i-1] = res;
                }
                resList.add(array);

            }
        }finally{
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
        }
        return resList;
    }
    public void commit() throws SQLException {
        conn.commit();
    }
    public void rollback() throws SQLException{
        conn.rollback();
    }
    public void close() throws SQLException{
        if(conn!=null){
            conn.close();
        }
    }
}
